﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace Db
{
    public class EmployeeDb : AbstractDb
    {

        public Employee GetEmployeeById(int id)
        {
            Employee emp = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Person JOIN Bif_Employee ON person_ID = employee_personID WHERE person_id = " + id);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("person_ID"));
                string email = q.GetString(q.GetOrdinal("person_email"));
                string fName = q.GetString(q.GetOrdinal("person_firstname"));
                string lName = q.GetString(q.GetOrdinal("person_lastname"));
                string phone = q.GetString(q.GetOrdinal("person_phone"));
                int postalcode = 0;
                //int postalcode = q.GetInt32(q.GetOrdinal("person_postalcode"));
                string city = null;
                //string city = q.GetString(q.GetOrdinal("city_city"));
                int salaryNr = q.GetInt32(q.GetOrdinal("employee_salaryNr"));
                string position = q.GetString(q.GetOrdinal("employee_position"));
                string password = q.GetString(q.GetOrdinal("person_password"));
                string cpr = q.GetString(q.GetOrdinal("employee_cpr"));

                emp = new Employee(uid, fName, lName, email, phone, salaryNr, position, postalcode, city, cpr, password);
            }

            return emp;
        }

        public void CreateEmployee(Employee newEmployee)
        {

            Statement stmt = new Statement("INSERT INTO Bif_Employee " +
                "(employee_salaryNr, employee_position, employee_CPR, person_ID, person_email, person_password, person_firstname, person_lastname, person_phone, person_postalcode) " +
                "VALUES(@Email, @FName, @LName, @Phone, @Postalcode, @salaryNr, @position, @CPR, @password)");

            stmt.AddParameter("@Email", newEmployee.Email, "string");
            stmt.AddParameter("@FName", newEmployee.FName, "string");
            stmt.AddParameter("@LName", newEmployee.LName, "string");
            stmt.AddParameter("@Phone", newEmployee.Phone, "string");
            //stmt.AddParameter("@Postalcode", newEmployee.Postalcode, "int");
            stmt.AddParameter("@salaryNr", newEmployee.SalaryNr, "int");
            stmt.AddParameter("@position", newEmployee.Position, "string");
            stmt.AddParameter("@CPR", newEmployee.Cpr, "string");
            stmt.AddParameter("@password", newEmployee.Password, "password");

            this.db.QueryInsert(stmt);
            
        }



        public void UpdateEmployee(Employee newEmployee)
        {

            Statement stmt = new Statement("INSERT INTO Bif_Employee " +
                "(employee_salaryNr, employee_position, employee_CPR, person_ID, person_email, person_password, person_firstname, person_lastname, person_phone, person_postalcode) Where personID = " + newEmployee.Id +
                "VALUES(@Email, @FName, @LName, @Phone, @Postalcode, @salaryNr, @position, @CPR, @password)");

            stmt.AddParameter("@Email", newEmployee.Email, "string");
            stmt.AddParameter("@FName", newEmployee.FName, "string");
            stmt.AddParameter("@LName", newEmployee.LName, "string");
            stmt.AddParameter("@Phone", newEmployee.Phone, "string");
            //stmt.AddParameter("@Postalcode", newEmployee.Postalcode, "int");
            stmt.AddParameter("@salaryNr", newEmployee.SalaryNr, "int");
            stmt.AddParameter("@position", newEmployee.Position, "string");
            stmt.AddParameter("@CPR", newEmployee.Cpr, "string");
            stmt.AddParameter("@password", newEmployee.Password, "password");

            this.db.QueryUpdate(stmt);
                        
        }


        public Employee GetEmployeeByCpr(string empcpr)
        {
            Employee emp = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Person JOIN Bif_Employee ON person_ID = employee_personID WHERE employee_CPR = " + empcpr);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("person_ID"));
                string email = q.GetString(q.GetOrdinal("person_email"));
                string fName = q.GetString(q.GetOrdinal("person_firstname"));
                string lName = q.GetString(q.GetOrdinal("person_lastname"));
                string phone = q.GetString(q.GetOrdinal("person_phone"));
                int postalcode = 0;
                //int postalcode = q.GetInt32(q.GetOrdinal("person_postalcode"));
                string city = "";
                //string city = q.GetString(q.GetOrdinal("city_city"));
                int salaryNr = q.GetInt32(q.GetOrdinal("employee_salaryNr"));
                string position = q.GetString(q.GetOrdinal("employee_position"));
                string password = q.GetString(q.GetOrdinal("person_password"));
                string cpr = q.GetString(q.GetOrdinal("employee_cpr"));

                emp = new Employee(uid, fName, lName, email, phone, salaryNr, position, postalcode, city, cpr, password);
            }

            return emp;
        }

        public void DeleteEmployeeByCpr(string empcpr)
        {

            Statement stmt = new Statement("DELETE * FROM Bif_Person JOIN Bif_Employee ON person_ID = employee_personID WHERE employee_CPR = " + empcpr);
            this.db.QueryDelete(stmt);
        }
    }
}
